package com.cxx.purchasecharge.dal.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.log4j.Logger;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.cxx.purchasecharge.core.model.GoodsStorageCourse;
import com.cxx.purchasecharge.core.model.GoodsStorageCourseSearchForm;
import com.cxx.purchasecharge.core.model.persistence.Goods;
import com.cxx.purchasecharge.dal.GoodsDaoPlus;
import com.cxx.purchasecharge.dal.common.GenericDaoUtils;

public class GoodsDaoImpl implements GoodsDaoPlus
{
    private static final Logger logger = Logger.getLogger (GoodsDaoImpl.class);

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List <Goods> findByFuzzyName (String keyWord)
    {
        logger.debug ("Find goods by fuzzy name: " + keyWord);
        String searchKey = (null == keyWord ? "" : keyWord.trim ());
        String sql = "select * from pc_goods g where g.name like ?1 order by convert(g.name USING gbk) COLLATE gbk_chinese_ci asc";
        Query query = entityManager.createNativeQuery (sql, Goods.class);
        query.setParameter (1, searchKey + "%");
        @SuppressWarnings ("unchecked")
        List <Goods> goodsList = query.getResultList ();
        return goodsList;
    }

    @Override
    public Page <Goods> findByFuzzy (Pageable pageable, String searchKey)
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging goods by fuzzy, pageable: (" + pageableLog + "), searchkey: " + searchKey);
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        String selectCountSql = "SELECT count(g) ";
        String fromSql = "FROM Goods g where " + "g.name like ?1 or " + "g.maker like ?2 or " + "g.barCode like ?3 or "
                         + "g.specification like ?4 or " + "g.technicalParameters like ?5 or "
                         + "g.importPrice like ?6 or " + "g.retailPrice like ?7 or " + "g.tradePrice like ?8";
        Query countQuery = entityManager.createQuery (selectCountSql + fromSql);
        countQuery.setParameter (1, keyWord + "%");
        countQuery.setParameter (2, keyWord + "%");
        countQuery.setParameter (3, keyWord + "%");
        countQuery.setParameter (4, keyWord + "%");
        countQuery.setParameter (5, keyWord + "%");
        countQuery.setParameter (6, keyWord + "%");
        countQuery.setParameter (7, keyWord + "%");
        countQuery.setParameter (8, keyWord + "%");
        int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

        String querySql = "SELECT g ";
        querySql += fromSql;
        String orderBySql = " ORDER BY ";
        querySql += orderBySql;
        String sortSqlStr = GenericDaoUtils.parseSort (Goods.class, pageable, "g");
        if (!"".endsWith (sortSqlStr))
        {
            querySql += sortSqlStr;
        }
        else
        {
            querySql += "g.name asc";
        }
        Query query = entityManager.createQuery (querySql);
        query.setParameter (1, keyWord + "%");
        query.setParameter (2, keyWord + "%");
        query.setParameter (3, keyWord + "%");
        query.setParameter (4, keyWord + "%");
        query.setParameter (5, keyWord + "%");
        query.setParameter (6, keyWord + "%");
        query.setParameter (7, keyWord + "%");
        query.setParameter (8, keyWord + "%");

        query.setFirstResult (pageable.getOffset ());
        query.setMaxResults (pageable.getPageSize ());

        @SuppressWarnings ("unchecked")
        List <Goods> goodsList = query.getResultList ();
        Page <Goods> goodsPage = new PageImpl <Goods> (goodsList, pageable, total);
        return goodsPage;
    }

    @Override
    public List <Goods> findGoodsType ()
    {
        String sql = "SELECT * FROM pc_goods g left join pc_goods_type gt on g.type_id=gt.id order by gt.name";
        // String sql =
        // "SELECT g FROM Goods g left outer join GoodsType gt on (g.type.id=gt.id) order by gt.name";
        Query query = entityManager.createNativeQuery (sql, Goods.class);
        // Query query = entityManager.createQuery (sql);
        @SuppressWarnings ("unchecked")
        List <Goods> goodsList = query.getResultList ();
        return goodsList;
    }

    @Override
    public Page <GoodsStorageCourse> findGoodsStorageCourse (Pageable pageable, GoodsStorageCourseSearchForm searchForm)
    {
        if (null != searchForm && searchForm.getGoodsId () != 0)
        {
            String countSql = "select count(*) from pc_order o, pc_customer c, pc_order_item oi, pc_goods g, pc_goods_storage gs where o.id=oi.order_id and o.customer_id=c.id and oi.goods_id=g.id and g.id=gs.goods_id and g.id=?1 ";
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                countSql += "o.createDate between ?2 and ?3 ";
            }
            countSql += "order by o.createDate desc";
            Query countQuery = entityManager.createNativeQuery (countSql);
            countQuery.setParameter (1, searchForm.getGoodsId ());
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                countQuery.setParameter (2, searchForm.getStartDate ());
                countQuery.setParameter (3, searchForm.getEndDate ());
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "select g.id as goodsId, g.name as goodsName, oi.unitPrice as goodsPrice, o.id as orderId, o.createDate as orderCreate, c.id as customerId, c.shortName as customerName, o.typeCode as orderTypeCode, o.statusCode as orderStatusCode, oi.amount as amount, gs.initialStock as initialStock, gs.currentStock as currentStock from pc_order o, pc_customer c, pc_order_item oi, pc_goods g, pc_goods_storage gs where o.id=oi.order_id and o.customer_id=c.id and oi.goods_id=g.id and g.id=gs.goods_id and g.id=?1 ";
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                querySql += "o.createDate between ?2 and ?3 ";
            }
            querySql += "order by o.createDate desc";
            Query queryQ = entityManager.createNativeQuery (querySql, GoodsStorageCourse.class);
            queryQ.setParameter (1, searchForm.getGoodsId ());
            if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
            {
                queryQ.setParameter (2, searchForm.getStartDate ());
                queryQ.setParameter (3, searchForm.getEndDate ());
            }

            if (null != pageable)
            {
                queryQ.setFirstResult (pageable.getOffset ());
                queryQ.setMaxResults (pageable.getPageSize ());
            }

            @SuppressWarnings ("unchecked")
            List <GoodsStorageCourse> goodsStorageCourses = queryQ.getResultList ();
            Page <GoodsStorageCourse> goodsStorageCoursePage = new PageImpl <GoodsStorageCourse> (goodsStorageCourses,
                                                                                                  pageable, total);

            return goodsStorageCoursePage;
        }
        return null;
    }

    @Override
    public List <GoodsStorageCourse> findGoodsStorageCourse (long goodsId)
    {
        String querySql = "select g.id as goodsId, g.name as goodsName, oi.unitPrice as goodsPrice, o.id as orderId, o.createDate as orderCreate, c.id as customerId, c.shortName as customerName, o.typeCode as orderTypeCode, o.statusCode as orderStatusCode, oi.amount as amount, gs.initialStock as initialStock, gs.currentStock as currentStock from pc_order o, pc_customer c, pc_order_item oi, pc_goods g, pc_goods_storage gs where o.id=oi.order_id and o.customer_id=c.id and oi.goods_id=g.id and g.id=gs.goods_id and g.id=?1 ";
        querySql += "order by o.createDate desc";
        Query queryQ = entityManager.createNativeQuery (querySql, GoodsStorageCourse.class);
        queryQ.setParameter (1, goodsId);

        @SuppressWarnings ("unchecked")
        List <GoodsStorageCourse> goodsStorageCourses = queryQ.getResultList ();
        return goodsStorageCourses;
    }

}
